package cn.itcast.store.dao.daoImp;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.store.dao.ProductDao;
import cn.itcast.store.domain.Product;
import cn.itcast.store.utils.JDBCUtils;

public class ProductDaoImp implements ProductDao {
	
	@Override
	public void saveProduct(Product product) throws Exception {
		String sql="INSERT INTO product VALUES(?,?,?,?,?,?,?,?,?,?)";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Object[] params={product.getPid(),product.getPname(),product.getMarket_price(),product.getShop_price(),product.getPimage(),product.getPdate(),product.getIs_hot(),product.getPdesc(),product.getPflag(),product.getCid()};
		qr.update(sql,params);
	}
	
	@Override
	public void editProduct(Product product) {
		//update product set  pname=?,market_price=? ,shop_price=?,pimage=?,pdate=?,is_hot=?,pdesc=?,pflag=?,cid=?where pid=?
		String sql="update product set  pname=? , market_price=? , shop_price=? , pimage=? , pdate=?,is_hot=?,pdesc=?,pflag=? , cid=? where pid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Object[] params={product.getPname(),product.getMarket_price(),product.getShop_price(),product.getPimage(),product.getPdate(),product.getIs_hot(),product.getPdesc(),product.getPflag(),product.getCid(),product.getPid()};
		try {
			qr.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	@Override
	public List<Product> findNewProducts() throws SQLException {
		String sql="SELECT * FROM product WHERE pflag=0 ORDER BY pdate DESC LIMIT 0 , 9";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Product>(Product.class));
		
	}

	@Override
	public List<Product> findHotProducts()throws SQLException  {
		String sql="SELECT * FROM product WHERE pflag=0 AND is_hot= 1 ORDER BY pdate DESC LIMIT 0 , 9";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Product>(Product.class));
	}

	@Override
	public Product findProductByPid(String pid) throws SQLException {
		
		String sql="select * from product where pid=?";
		//String sql="SELECT * FROM product WHERE pid=？";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		//System.out.println(pid);
		//System.out.println(qr.query(sql, new BeanHandler<Product>(Product.class),pid));
		//       qr.query(sql, new BeanHandler<Product>(Product.class),pid);
		return qr.query(sql, new BeanHandler<Product>(Product.class),pid);
	}

	
	
	@Override
	public int findTotalNum(String cid) throws SQLException {
		String sql="select count(*) from product where cid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql, new ScalarHandler(),cid);
		return num.intValue();
	}

	@Override
	public List<Product> findProductsWithCidAndPage(String cid, int startIndex, int pageSize) throws SQLException {
		
		
		String sql="select * from product where cid=? and pflag = 0  limit ?,?";
		//String sql="SELECT * FROM product WHERE cid=? LIMIT ?,?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Product>(Product.class),cid,startIndex,pageSize);
		
	}
	@Override
	public List<Product> findProductWithPage(int startIndex, int pageSize) throws SQLException {
		String sql="select * from product where pflag=0 limit ?,?";
		//String sql="SELECT * FROM product WHERE cid=? LIMIT ?,?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Product>(Product.class),startIndex,pageSize);
	}
	@Override
	public int finProductTotalRecords() throws SQLException {
		String sql="select count(*) from product where pflag =0";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql, new ScalarHandler());
		return num.intValue();
	}

	public int findPflagProductTotalRecords() throws SQLException {
		String sql="select count(*) from product where pflag =1";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql, new ScalarHandler());
		return num.intValue();
	}

	@Override
	public List<Product> findFlagProductWithPage(int startIndex, int pageSize) throws SQLException {
		String sql="select * from product where pflag=1  limit ?,?";
		//String sql="SELECT * FROM product WHERE cid=? LIMIT ?,?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Product>(Product.class),startIndex,pageSize);
	}

	@Override
	public void deleteProductsByPid(String pid, int flag) {
		String sql="update product set pflag=? where	pid =?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		try {
			qr.update(sql, flag,pid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	

	

	
	
	
	
	
	
}
